package com.situ.util.dao;
import java.util.List;
import java.util.Map;

import com.situ.util.entity.*;
import com.situ.login.entity.SysUser;
import org.apache.ibatis.annotations.*;
/**
 * CstCustomer的Dao接口
 * 
 * @author 
 *
 */
@Mapper
public interface CstCustomerDao {
	//根据条件查询客户表
@Select("<script> select *,case \n" +
		"  when \n" +
		"   exists (select * from orders o   where  c.cust_no=o.odr_customer_id and o.odr_status=1)\n" +
		"  then '1'\n" +
		"  end back_status\n" +
		"  from cst_customer c  \n" +
		"   <where>" +
		" c.cust_status='正常' and cust_manager_id=#{cust_manager_id}" +
		"  <if test=\"cust_no!=null and cust_no!=''\">\n " +
		" and c.cust_no like '%${cust_no}%' </if> \n" +
		"  <if test=\"cust_name!=null and cust_name!=''\">\n " +
		" and c.cust_name like '%${cust_name}%' </if> \n" +
		"  <if test=\"cust_region!=null and cust_region!=''\">\n " +
		" and c.cust_region=#{cust_region} </if> \n" +
		"  <if test=\"cust_manager_name!=null and cust_manager_name!=''\">\n " +
		" and c.cust_manager_name like '%${cust_manager_name}%' </if> \n" +
		"  <if test=\"cust_level_label!='' and cust_level_label!=null\">\n " +
		"  and c.cust_level_label=#{cust_level_label} </if> " +
		" </where> </script>")
	public List<Map> queryallCustomer(CstCustomer cst);
//根据id查客户表
@Select("select * from cst_customer where cust_no=#{id}")
	public CstCustomer showCustById(@Param("id") String cust_no);

//查数据字典，用于下拉列表
	@Select("select * from bas_dict where dict_type=#{dict_type}")
	public List<BasDict> querydict(@Param("dict_type") String dict_type);

	//根据dict_item查各户等级的dict_value
	@Select("select distinct dict_value from bas_dict where dict_item=#{dict_item}")
	public Integer querylevelid(@Param("dict_item") String dict_item);

	//查客户经理
	@Select("select * from sys_user u inner join sys_role r on r.role_id=u.usr_role_id  where u.usr_flag=1 and r.role_flag=1 and r.role_name='客户经理'")
	public List<SysUser> queryusers();

	//根据经理名称查经理id
	@Select("select usr_id from sys_user where usr_name=#{usr_name}")
	public Integer queryuseridbyname(@Param("usr_name") String usr_name);

//查询客户的联系人
	@Select("select * from cst_linkman where lkm_cust_no=#{lkm_cust_no}")
public List<CstLinkman> queryLinkmanById(@Param("lkm_cust_no") String lkm_cust_no);

	//查询客户的交往记录
	@Select("\n" +
			"  select * from cst_activity where atv_cust_no=#{atv_cust_no}")
	public List<CstActivity> queryatvById(@Param("atv_cust_no") String atv_cust_no);

	//根据id交往记录表
	@Select("\n" +
			"  select * from cst_activity where atv_id=#{atv_id}")
	public CstActivity queryatvByaId(@Param("atv_id") Integer atv_id);
	//查询客户的交往记录
	@Select("select * from orders where odr_customer_id=#{id} order by odr_id desc")
	public List<Orders> queryorders(@Param("id") String odr_customer_id);
	//查详细历史订单
	@Select("select *,(select SUM(odd_price) from orders_line where odd_order_id=o.odr_id)as sum from orders o where o.odr_id=#{odr_id}")
	public Map queryorderbyid(@Param("odr_id") Integer odr_id);
//查询历史订单的产品与订单连接
	@Select("select *,(select prod_name from product  where prod_id=l.odd_prod_id) as name,\n" +
			"(select prod_price from product  where prod_id=l.odd_prod_id)as price  \n" +
			"from orders_line l where l.odd_order_id=#{odd_order_id}")
	public List<Map> queryPLByid(@Param("odd_order_id") Integer odd_order_id);
	//增加联系人
    @Select(" insert into cst_linkman(lkm_cust_no,lkm_cust_name,lkm_name,lkm_sex,lkm_postion ,lkm_tel,lkm_mobile,lkm_memo)\n" +
            "    values(#{lkm_cust_no},#{lkm_cust_name},#{lkm_name},#{lkm_sex},#{lkm_postion},#{lkm_tel},#{lkm_mobile},#{lkm_memo})")
  public void addlinkman(CstLinkman c);

    //增加交往记录
	@Select("insert into cst_activity(atv_cust_no,atv_cust_name,atv_date,atv_place,atv_title ,atv_desc) \n" +
			"  values(#{atv_cust_no},#{atv_cust_name},#{atv_date},#{atv_place},#{atv_title},#{atv_desc})")
	public void addatv(CstActivity a);

	//修改客户信息
	@Select(" update  cst_customer set cust_name=#{cust_name},cust_region=#{cust_region},cust_manager_id=#{cust_manager_id},cust_manager_name=#{cust_manager_name},cust_level=#{cust_level},cust_level_label=#{cust_level_label}\n" +
			",cust_satisfy=#{cust_satisfy},cust_credit=#{cust_credit},cust_addr=#{cust_addr},cust_zip=#{cust_zip},cust_tel=#{cust_tel},cust_fax=#{cust_fax} ,cust_website=#{cust_website},cust_licence_no=#{cust_licence_no},cust_chieftain=#{cust_chieftain}\n" +
			",cust_bankroll=#{cust_bankroll},cust_turnover=#{cust_turnover},cust_bank=#{cust_bank},cust_bank_account=#{cust_bank_account},cust_local_tax_no=#{cust_local_tax_no},cust_national_tax_no=#{cust_national_tax_no} where cust_no=#{cust_no}\n" +
			" ")
	public void updateCustomerById(CstCustomer c);

//修改联系人
    @Select("update cst_linkman set lkm_name=#{lkm_name},lkm_sex=#{lkm_sex},lkm_postion=#{lkm_postion} ,lkm_tel=#{lkm_tel},lkm_mobile=#{lkm_mobile},lkm_memo=#{lkm_memo}\n" +
            "    where lkm_id=#{lkm_id}")
    public void updatelinkman(CstLinkman c);

	//修改交往记录
	@Select("update cst_activity set atv_date=#{atv_date},atv_place=#{atv_place},atv_title=#{atv_title} ,atv_desc=#{atv_desc} where atv_id=#{atv_id}")
	public void updateatv(CstActivity c);

	//客户流失
	@Select("update cst_customer set cust_status='已流失' where  cust_no=#{cust_no}")
	public void delcustomer(@Param("cust_no") String cust_no);

	//删除联系人
   @Select("delete cst_linkman where  lkm_id=#{lkm_id}")
    public void dellinkman(@Param("lkm_id") Integer lkm_id);
	//删除交往记录
	@Select("delete cst_activity where  atv_id=#{atv_id}")
	public void delatv(@Param("atv_id") Integer atv_id);
















}